package com.navi.loaddata.loadService.impl.opeEqptData;

import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.pojo.OpeData;
import com.navi.loaddata.core.pojo.OpeKey;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * LoadOpeEqptDataService
 *
 * @author Matthew
 * @date 2018-05-04
 */
@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager",
    rollbackFor = {RuntimeException.class, Exception.class})
public class LoadOpeDataService {

  private static final Logger logger = LoggerFactory.getLogger(LoadOpeDataService.class);

  @Autowired
  private GreenPlumRepositoryImpl gpDao;

  public int loadOpeDataMain(EtlConfD etlConfD) throws ETLException {
    int countSize = 0;
    Session session = gpDao.getEntityManagerSession();
    try {
      countSize = loadOpeData(session, etlConfD);
      logger.info("lodEdcData finish");
    } catch (ETLException e) {
      throw new ETLException(e.getMessage());
    }
    return countSize;
  }
  private int loadOpeData(Session session, EtlConfD etlConfD) throws ETLException {
    String startTime = etlConfD.getRunStartTimestamp().toString().substring(0, 19);
    String endTime = etlConfD.getRunEndTimestamp().toString().substring(0, 19);
    QueryRunner runnerGp = JdbcUtil.getQueryRunner_gp();
    QueryRunner runnerMes = JdbcUtil.getQueryRunner_mes();
    String opekeySql = "SELECT ope_id as opeId, ope_ver as opeVer, shop_id as shopId FROM m_ope_d GROUP BY ope_id, ope_ver, shop_id ORDER BY ope_id ";
    String opeDataSql = "SELECT O.PROCESSOPERATIONNAME AS opeId, O.PROCESSOPERATIONVERSION AS opeVer, O.DESCRIPTION AS opeDsc, "
        + " CASE WHEN PROCESSOPERATIONTYPE ='Inspection' THEN 'M'WHEN PROCESSOPERATIONTYPE='Production' THEN 'P' "
        + " WHEN PROCESSOPERATIONTYPE='Production,Inspection' THEN 'M'WHEN PROCESSOPERATIONTYPE='Repair' THEN 'R' "
        + " WHEN PROCESSOPERATIONTYPE='Rework' THEN 'W'ELSE PROCESSOPERATIONTYPE END AS opeType, "
        + " CASE WHEN SUBFACTORYNAME='ARRAY' THEN 1 WHEN SUBFACTORYNAME='CF' THEN 2 WHEN SUBFACTORYNAME='OC' THEN 2 ELSE 0 END AS shopKeyFk, "
        + " O.SUBFACTORYNAME AS shopId, O.LASTUPDATEUSER AS evtUser, O.LASTUPDATETIME AS evtTimestamp, 'Y' AS mesOpeFlg "
        + " FROM MES_PROCESSOPERATIONSPEC O WHERE O.LASTUPDATETIME > to_date('" + startTime + "','yyyy/mm/dd HH24:MI:SS') "
        + " AND O.LASTUPDATETIME <= to_date('" + endTime + "','yyyy/mm/dd HH24:MI:SS') "
        + " AND PROCESSOPERATIONTYPE IN ('Inspection','Production','Production,Inspection','Repair','Rework') "
        + " AND nvl(SUBSTR(PROCESSOPERATIONNAME, 6,7),'X') != 'D'"
        + " AND (PROCESSOPERATIONNAME LIKE 'A%' OR PROCESSOPERATIONNAME LIKE 'C%' OR PROCESSOPERATIONNAME LIKE 'L%')";

    List<OpeData> opeDatas;
    List<OpeKey> opeKeys;
    int count = 0;
    try {
      opeKeys = runnerGp.query(opekeySql, new BeanListHandler<>(OpeKey.class));
      opeDatas = runnerMes.query(opeDataSql, new BeanListHandler<>(OpeData.class));

      for (int i = 0; i < opeDatas.size(); i++) {
        OpeData opeData = opeDatas.get(i);
        // 如果不在M_OPE_D表内
        OpeKey opeKey = new OpeKey();
        opeKey.setOpeId(opeData.getOpeId());
        opeKey.setOpeVer(opeData.getOpeVer());
        opeKey.setShopId(opeData.getShopId());
        boolean isIn = false;
        for (int j = 0; j < opeKeys.size(); j++) {
          OpeKey o = opeKeys.get(j);
          if (o.getOpeId().equals(opeData.getOpeId())) {
            if (o.getShopId().equals(opeData.getShopId())) {
              isIn = true;
            }
          }
        }
        if (!isIn) {
          StringBuilder insertSql = new StringBuilder("INSERT INTO M_OPE_D (OPE_ID, OPE_VER, OPE_DSC, OPE_TYPE, SHOP_KEY_FK, SHOP_ID, "
              + "EVT_USER, EVT_TIMESTAMP, ETL_TIMESTAMP, MES_OPE_FLG) VALUES (");
          if (null != opeData.getOpeId()) {
            insertSql.append("'").append(opeData.getOpeId()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != opeData.getOpeVer()) {
            insertSql.append("'").append(opeData.getOpeVer()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != opeData.getOpeDsc()) {
            insertSql.append("'").append(opeData.getOpeDsc()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != opeData.getOpeType()) {
            insertSql.append("'").append(opeData.getOpeType()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != opeData.getShopKeyFk()) {
            insertSql.append("'").append(opeData.getShopKeyFk()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != opeData.getShopId()) {
            insertSql.append("'").append(opeData.getShopId()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != opeData.getEvtUser()) {
            insertSql.append("'").append(opeData.getEvtUser()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != opeData.getEvtTimestamp()) {
            insertSql.append("'").append(opeData.getEvtTimestamp()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          insertSql.append("'").append(Timestamp.valueOf(LocalDateTime.now())).append("',");
          insertSql.append("'").append(opeData.getMesOpeFlg()).append("'");
          insertSql.append(")");
          SQLQuery queryLoadStep = session.createSQLQuery(insertSql.toString());
          count +=   queryLoadStep.executeUpdate();
          logger.info("已同步站点{}_{}", opeData.getOpeId(), opeData.getShopId());
        }
      }

      logger.info("时间段:[{}]-[{}]共发现{}数据", startTime, endTime, opeDatas.size());

    } catch (SQLException e) {

      logger.error(etlConfD.getJobName() + "LoadEdcDataService:lodEdcData" + LoggerUtil.stackTraceToString(e));

    }

    return count;

  }


}
